package com.rzl.expense.quartz.task;

import com.rzl.expense.common.utils.StringUtils;
import org.apache.commons.collections4.CollectionUtils;

import java.util.List;
import java.util.stream.Collectors;

/**
 * @ClassName TwrSqlAdapter
 * @Author TMY
 * @Date 2022/8/23 19:42
 */
public class RuleSqlAdapter {

    /**
     * 获取集团用户数据
     *
     * @return
     */
    public static String getObtainingGroupUserData() {
        StringBuilder sb = new StringBuilder();
        sb.append("select su.user_id          as userId, " +
                "       su.reg_id           as regId, " +
                "       su.user_code        as userCode, " +
                "       su.user_loginname   as userLoginname, " +
                "       su.user_name        as userName, " +
                "       su.user_password    as userPassword, " +
                "       su.user_phone       as userPhone, " +
                "       su.user_email       as userEmail, " +
                "       su.user_note        as userNote, " +
                "       su.user_state       as userState, " +
                "       date_format(su.create_time,'%Y-%m-%d %H:%i')  as createTime, " +
                "        date_format(su.modify_time,'%Y-%m-%d %H:%i') as modifyTime, " +
                "       su.is_valid         as isValid, " +
                "       su.smap_id          as smapId, " +
                "       su.preferred_mobile as preferredMobile, " +
                "       su.smap_status      as smapStatus, " +
                "      date_format(su.change_time,'%Y-%m-%d %H:%i') as changeTime, " +
                "       su.syn_kind         as synKind, " +
                "       su.change_type      as changeType, " +
                "       su.org_code         as orgCode, " +
                "       su.city_name        as cityName, " +
                "       su.prv_id           as prvId, " +
                "      date_format(su.last_modify_time,'%Y-%m-%d %H:%i') as lastModifyTime, " +
                "      (select GROUP_CONCAT(role_id) from sys_roleuser where relation_state=0 and user_id=su.user_id) as roleIdStr " +
                "       from sys_user su " +
                "       where su.user_state = 0");
        return sb.toString();
    }

    public static String getTwrRent(String date, int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from twr_rent_ctl where yearmonth='" + date + "' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getDatContract(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from dat_contract limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getRentContract(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rent_contract where create_time>='"+currentDate[0]+"' and create_time<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getRentBillAccount(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rent_billaccount limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getRentBillAccountResource(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rent_billaccountresource "+
                "where relation_startdate>='"+currentDate[0]+"' and relation_startdate<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getRentBillAccountContract(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rent_billaccountcontract limit " + startIndex + "," + limit);
        return sb.toString();
    }
    public static String getEleBillAccount(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
//        sb.append("select count(*) as total from  ele_billaccount");
        sb.append("select billaccount_id,prv_id,prv_sname,preg_id,preg_name,reg_id,reg_name,billaccount_code,billaccount_name,billaccount_state,billaccount_type,auditing_state,auditing_user_id,auditing_date,plan_date,calc_multi,payment_state,billaccount_note,auditing_role_code,belong_dept,is_special,enter_user_id,delete_reason,correct_id,correct_time,update_user_id,create_user_id,update_time,billaccount_remarke,last_modify_time,stay_auditing_user_id,cost_center,cost_center_name,last_pass_billaccount_name,transfertostraight_flag,transfertostraight_date,search_keywords,tele_cmn_serv_sys_ratio,tele_cmn_serv_actual_ratio,tele_cmn_serv_remark,tele_cmn_serv_useredit,tele_cmn_serv_isedit,last_billamount_startdate,last_billamount_enddate,revoke_remark,new_energy_billaccount_type,amount_type,basesite_ele_share_startdate from  ele_billaccount limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getEleAmortizeInfo(String[] currentDate, int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ele_amortize_info where push_date>='"+currentDate[0]+"' and push_date<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getRptPrvEleSitePayable(Long year,Long month, int startIndex, int limit) {

        StringBuilder sb = new StringBuilder();
        sb.append("select * from rpt_prv_ele_site_payable where on_year=" + year + " and on_month=" + month+" limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getPaymentResourceDetail(String startDate,String endDate, int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from payment_resource_detail " +
                "where billamount_date>='"+startDate+"' and billamount_date<='"+endDate+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }


    public static String getElePaymentDetail(String[] currentDate, int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ele_paymentdetail " +
                "where create_data_time>='"+currentDate[0]+"' and create_data_time<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    //电费核销单
    public static String getEleVerificationDetail(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ele_verificationdetail limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getEleVerification(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ele_verification limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getDatBaseTower(String[]currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from dat_basetower where (create_time >='"+currentDate[0]+"' and create_time <='"+currentDate[1]+"') or " +
                "(update_time >='"+currentDate[0]+"' and update_time <='"+currentDate[1]+"') limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getRptPrvTowerKeyIndexCope(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rpt_prv_tower_key_index_cope limit " + startIndex + "," + limit);
        return sb.toString();
    }


    public static String getEleBillAccountToResource(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
//        sb.append("select count(*) as total from  ele_billaccountbaseresource");
        sb.append("select * from  ele_billaccountbaseresource " +
                "where last_modify_time>='"+currentDate[0]+"' and last_modify_time<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    //电费缴费汇总
    public static String getEleBillAmount(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
//        sb.append("select count(*) as total from  ele_billamount");
        sb.append("select * from  ele_billamount limit " + startIndex + "," + limit);
        return sb.toString();
    }

    //电费账单明细
    public static String getEleBillAmountDetail(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from  ele_billamountdetail where payment_date>='"+currentDate[0]+"' and payment_date<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();

    }
    public static String getBaseSite(String[] currentDate,int startIndex,int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from  dat_basesite where (create_time >='"+currentDate[0]+"' and create_time <='"+currentDate[1]+"') or " +
                "(update_time >='"+currentDate[0]+"' and update_time <='"+currentDate[1]+"') limit " + startIndex + "," + limit);
        return sb.toString();

    }

    //报账点缴费信息
    public static String getElePayment(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ele_payment where billamount_date>='"+currentDate[0]+"' and billamount_date<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    //报账点缴费信息
    public static String getSiteCode(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT  " +
                "dat.tower_site_code, " +
                "dat.baseresource_name, " +
                "dat.baseresource_cuid, " +
                "elebill.billamount_state, " +
                "pay.billamount_startdate, " +
                "pay.billamount_enddate " +
                "FROM dat_baseresource dat " +
                "LEFT JOIN ele_billaccountbaseresource bil on dat.baseresource_id = bil.baseresource_id " +
                "LEFT JOIN ele_billamountdetail mount on mount.billaccount_id = bil.billaccount_id " +
                "left join ele_billamount elebill on elebill.billamount_id = mount.billamount_id " +
                "left join ele_payment pay on pay.billamount_id = elebill.billamount_id " +
                "where pay.is_finance in (1, 2) " +
                "and dat.tower_site_code is not null " +
                "and dat.tower_site_code != '无' " +
                "and dat.tower_site_code != '' " +
                "and pay.billamount_enddate >= '2024-05-01' " +
                "and  pay.billamount_startdate< '2024-11-01' " +
                "group by dat.tower_site_code");
        return sb.toString();
    }


    public static String getDatBaseResource(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from  dat_baseresource where (create_time >='"+currentDate[0]+"' and create_time <='"+currentDate[1]+"') or " +
                "(update_time >='"+currentDate[0]+"' and update_time <='"+currentDate[1]+"') limit " + startIndex + "," + limit);
        return sb.toString();
    }

    //租赁费账单表
    public static String getTowerBillBalance(String date, int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from twr_towerbillbalance where account_peroid='" + date + "' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getTowerSiteCode(String date) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT dat.baseresource_cuid,dat.baseresource_name,dat.tower_site_code,pay.billamount_startdate,pay.billamount_enddate  " +
                "FROM dat_baseresource dat LEFT JOIN ele_billaccountbaseresource bil on dat.baseresource_id = bil.baseresource_id  " +
                "LEFT JOIN tele_billamountdetail mount on mount.billaccount_id = bil.billaccount_id  " +
                "left join tele_billamount elebill on elebill.billamount_id = mount.billamount_id  " +
                "left join tele_payment pay on pay.billamount_id = elebill.billamount_id  " +
                "where dat.tower_site_code is not null and dat.tower_site_code != '无' and dat.tower_site_code != '' and pay.billamount_enddate>= '2024-05-01' and pay.billamount_enddate< '2024-11-01'  " +
                "group by dat.tower_site_code");
        return sb.toString();
    }

    //铁塔室分传输微站非标月报快照
    public static String getTowerBillBalanceShoot(String date, int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from twr_towerbillbalance_snapshoot where account_peroid='" + date + "' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    //租费计提单
    public static String getRentAccrual(String date,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select *,abstract as rent_abstract from rent_accrual where `year_month`='"+date+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getDatElectricMeter(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select meter_id, prv_id, prv_sname, preg_id, preg_name, reg_id, reg_name, billaccount_id, meter_code, initial_value, upper_value, flat_value, peak_value, top_value, valley_value, flat_upper_value, peak_upper_value, valley_upper_value, top_upper_value, meter_type, electricmeter_multiply, account_number, meter_state, is_share, meter_note, data_from, sys_meter_code, replace_date, replace_meter_id, create_user_id, update_user_id, ele_tricmeter_type, " +
                "tower_meter_id, tricmeter_property_code, correct_id,auditing_state,last_auditingdate,auditing_user_id,stay_auditing_user_id,submit_user_id,replace_meter_code,reset_meter_date,is_show_replace,is_show_reset,is_can_replace,last_modify_time " +
                "from dat_electricmeter where last_modify_time>='"+currentDate[0]+"' and last_modify_time<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getDatElectricMeterResource(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ele_baseresourceelectricmeter  where last_modify_time>='"+currentDate[0]+"' and last_modify_time<='"+currentDate[1]+"' " + startIndex + "," + limit);
        return sb.toString();
    }
    //租费计提单
    public static String getRentTwrCtlRoom(String date,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from twr_rent_ctl_room where yearmonth='"+date+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }
    public static String getEleContractBillaccount(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ele_contractbillaccount where last_modify_time>='"+currentDate[0]+" and last_modify_time<='"+currentDate[1]+"'' limit " + startIndex + "," + limit);
        return sb.toString();
    }
    public static String getEleContract(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ele_contract where last_modify_time>='"+currentDate[0]+" and last_modify_time<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }
    //房租费缴费记录
    public static String getRentPayment(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rent_payment where create_data_time>='"+currentDate[0]+"' and create_data_time<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    //房租费缴费明细
    public static String getRentPaymentDetail(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rent_paymentdetail limit " + startIndex + "," + limit);
        return sb.toString();
    }
    //房租费缴费明细
    public static String getRentBillamount(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rent_billamount where billamount_date >='"+currentDate[0]+"' and billamount_date<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }
    public static String getRptPrvRentBasesiteInfo(String[] yearMonth,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rpt_prv_rent_basesite_info where on_year = '"+yearMonth[0]+"' and on_month = '"+yearMonth[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }
        public static String getRptPrvRentBasesiteResource(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rpt_prv_rent_basesite_resource limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getRentOtherAmountFinance(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rent_other_amount_finance limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getRptPrvRentBasesitePayable(String[] yearMonth,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rpt_prv_rent_basesite_payable where on_year = '"+yearMonth[0]+"' and on_month = '"+yearMonth[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getRptPrvRentBasesitePayableTower(String[] yearMonth,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rpt_prv_rent_basesite_payable_tower where on_year = '"+yearMonth[0]+"' and on_month = '"+yearMonth[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }
    //房租费缴费明细
    public static String getRentBillamountDetail(String[] currentDate,int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from rent_billamountdetail where create_time>='"+currentDate[0]+"' and create_time<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getTwrHighTower(String date) {
        StringBuilder sb = new StringBuilder();

        sb.append(StringUtils.format("SELECT " +
                        "trc.yearmonth as sta_date, " +
                        "(select count(1) from twr_rent_ctl where reg_id IS NOT NULL and yearmonth='{}' and reg_id is not NULL) as all_count, " +
                        "vsr.preg_id AS preg_id, " +
                        "vsr.preg_name AS preg_name, " +
                        "vsr.reg_name as reg_name, " +
                        "vsr.reg_id as reg_id, " +
                        "trc.agree_bill_code agree_bill_code, " +
                        "trc.business_confirm_number business_confirm_number, " +
                        "trc.tower_station_code tower_station_code, " +
                        "trc.site_name as tower_station_name, " +
                        "trc.tower_type tower_type, " +
                        "trc.unit1_count, " +
                        "trc.unit2_count, " +
                        "trc.unit3_count, " +
                        "(ifnull(trc.unit1_count,0)+ifnull(trc.unit2_count,0)+ifnull(trc.unit3_count,0)) as unit_count, " +
                        "case " +
                        "  when trc.tower_type = '1110'  then '普通地面塔' " +
                        "when trc.tower_type = '1120'  then '景观塔' ELSE NULL END AS tower_type_name, " +
                        "trc.share_info, " +
                        "case " +
                        "  when trc.share_info = '01'  then '新建首家' " +
                        "when trc.share_info = '02'  then '新建共享' " +
                        "when trc.share_info = '03'  then '无既有共享的原产权方' " +
                        "when trc.share_info = '04'  then '存量既有共享' " +
                        "when trc.share_info = '05'  then '存量新增共享' " +
                        "when trc.share_info = '051'  then '存量产权方新增共享' " +
                        "when trc.share_info = '052'  then '存量非产权方新增共享' " +
                        "when trc.share_info = '06'  then '暂无' " +
                        "when trc.share_info = '07'  then '有既有共享的原产权方' ELSE NULL END AS share_info_name, " +
                        "trc.active_time " +
                        "FROM twr_rent_ctl trc " +
                        "LEFT JOIN sys_region vsr ON trc.reg_id = vsr.reg_id " +
                        "WHERE trc.reg_id IS NOT NULL and yearmonth='{}' and (trc.tower_type='1110' or trc.tower_type='1120')", date,
                date));
        return sb.toString();
    }

    public static String getTwrHighRoom(String date) {
        StringBuilder sb = new StringBuilder();

        sb.append(StringUtils.format("SELECT " +
                        "trc.yearmonth as sta_date, " +
                        "(select count(1) from twr_rent_ctl where reg_id IS NOT NULL and yearmonth='{}' and reg_id is not NULL) as all_count, " +
                        "vsr.preg_id AS preg_id, " +
                        "vsr.preg_name AS preg_name, " +
                        "vsr.reg_name as reg_name, " +
                        "vsr.reg_id as reg_id, " +
                        "trc.agree_bill_code agree_bill_code, " +
                        "trc.business_confirm_number business_confirm_number, " +
                        "trc.tower_station_code tower_station_code, " +
                        "trc.site_name as tower_station_name, " +
                        "ifnull(trc.unit1_count,0) as unit1_count, " +
                        "ifnull(trc.unit2_count,0) as unit2_count, " +
                        "ifnull(trc.unit3_count,0) as unit3_count, " +
                        "(ifnull(trc.unit1_count,0)+ifnull(trc.unit2_count,0)+ifnull(trc.unit3_count,0)) as unit_count, " +
                        "trc.room_type, " +
                        "case " +
                        "when trc.room_type = '01'  then '自建砖混机房' " +
                        "when trc.room_type = '02'  then '自建框架机房' " +
                        "when trc.room_type = '03'  then '自建彩钢板机房' " +
                        "when trc.room_type = '06'  then '其他机房' " +
                        "when trc.room_type = '08'  then '一体化机房' ELSE NULL END AS room_type_name, " +
                        "trc.room_share_info, " +
                        "case " +
                        "when trc.room_share_info = '01'  then '新建首家' " +
                        "when trc.room_share_info = '02'  then '新建共享' " +
                        "when trc.room_share_info = '03'  then '无既有共享的原产权方' " +
                        "when trc.room_share_info = '04'  then '存量既有共享' " +
                        "when trc.room_share_info = '05'  then '存量新增共享' " +
                        "when trc.room_share_info = '051'  then '存量产权方新增共享' " +
                        "when trc.room_share_info = '052'  then '存量非产权方新增共享' " +
                        "when trc.room_share_info = '06'  then '暂无' " +
                        "when trc.room_share_info = '07'  then '有既有共享的原产权方' ELSE NULL END AS room_share_info_name, " +
                        "trc.active_time, " +
                        "sysdate() as create_time " +
                        "FROM twr_rent_ctl trc " +
                        "LEFT JOIN sys_region vsr ON trc.reg_id = vsr.reg_id " +
                        "WHERE trc.reg_id IS NOT NULL and yearmonth='{}' and (trc.room_type='01' or trc.room_type='02' or trc.room_type='03' or trc.room_type='06' or trc.room_type='08')", date,
                date));
        return sb.toString();
    }

    public static String getOilPowerFee(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();

        sb.append("select * from twr_oil_power_fee limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getContractHistory(int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
//        sb.append("select * from ele_amortize_info where substring(billamount_startdate,1,7)='" + date + "'");
        sb.append("select * from ele_contract_history limit " + startIndex + "," + limit);
        return sb.toString();
    }

    public static String getContractBackups(String[] currentDate, int startIndex, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ele_contract_backups " +
                "where create_time>='"+currentDate[0]+"' and create_time<='"+currentDate[1]+"' limit " + startIndex + "," + limit);
        return sb.toString();
    }
}
